The invention relates generally to the field of digital data processing systems, and more particularly to data base management systems (DBMS""s). The invention specifically provides a new and improved system and method of identifying and resolving conflicts among versions of a database table.
Databases are generally used to store large amounts of information in a structured manner for subsequent retrieval and processing. Databases, in turn, are managed by data base management systems (DBMS""s). A DBMS allows one to submit a query identifying a number of criteria for the information that is to be retrieved from the database. Queries may be submitted by a user, in which case the DBMS can return the subset of information stored in the database that conforms to that identified by the query for, for example, display to the user. Alternatively, queries may be submitted by an application program, in which case the DBMS can return the information to the application program for processing. The DBMS can also receive information from an user or application program for storage in the database, and the DBMS can, after receiving the information, use the information to update the database.
In one type of DBMS, generally referred to as a relational database system, information is stored in tables, with each table comprising a two-dimensional array organized a plurality of rows and columns. Information stored in a relational database system is in the form of records, with each record comprising one or more fields each associated with an attribute. For example, an organization such as a company or a government agency might maintain a database identifying its employees, and in that case each record may be associated with each employee, with attributes including information such as an employee number, name, address, department, office location, salary, date of hire, tax information and other information that the organization would deem useful in managing its employees. Similarly, a membership organization such as a club or an association may maintain a database identifying its members, and attributes may include information such as a member number, name, address, membership classification if the organization has various classes of membership, renewal data, and other information that the organization would deem useful. In a table in a relational database system, the attributes associated with the records are stored in the same order, so that for all records the xe2x80x9cj-thxe2x80x9d field will be associated with the same attribute. Generally, information from one or a combination of predetermined ones of the attributes of each record will be selected to comprise a record xe2x80x9ckey,xe2x80x9d whose value is used to uniquely identify the respective record.
Database queries issued by an user or an application program can be based on any attribute, including but not limited to the attributes selected to define the record keys. A query can, for example, ask for information from a record or records having a particular key value, set of key values or range of key values, in which case the DBMS will return information from the records, if any, whose key value or values correspond to those set forth in the query. Similarly, a query can ask for information from records based on criteria relating to the other attributes, in which case the DBMS will return information from the records, if any, whose attributes contain the value or values that correspond to those set forth in the query. In addition, a query can ask the DBMS to establish a new record, in which case the DBMS will add a row to the table in which information for the record can be stored; the information to be stored in the new record may be provided with the query or in a later query. A query can ask the DBMS to update one or more records, in which case the query will identify the records to be updated, in a manner similar to that described above for a retrieval, and the information to be used in the update; in response to such a query, the DBMS will use the information contained in the query to update the identified records. Alternatively, a query can ask the DBMS to delete one or more records, in which case the DBMS will delete the records; the records to be deleted can be identified in the query in a similar manner to that described above for a retrieval.
In many kinds of applications, it is desirable to allow a user to make use of one or more tables, or portions thereof, of a database as they exist at a particular point in time and update the information therein, with the updates not being visible to other users while the information is being updated. Currently, that is accomplished by means of a check-out/check-in scheme. In such a scheme, a user xe2x80x9cchecks outxe2x80x9d the database table or tables, or portions thereof, (hereinafter xe2x80x9ctablesxe2x80x9d), that he or she wishes to use, after which the DBMS makes a copy of the tables that have been checked out, from the original xe2x80x9clivexe2x80x9d database. Thereafter, the user can operate with the copy, updating the information in his or her copy as appropriate. While the one user is operating with his or her copy, other users can use the live database, updating the information therein, including information in the tables that have been checked out. When the user has finished, he or she can check the checked-out portion back into the database, in the process updating the live database as necessary. For example, for a database that is used in connection with a computer-assisted design project, a user working on one aspect of a design may wish to check out a portion of the database reflecting that aspect at one point in the design process, make changes to the design, and, if and when he or she is satisfied with the changes, update the database with the changes. As a specific example, if the live database is used in connection with design of an airport, a user may which to check out a portion of the database reflecting the design of a terminal or a particular facility of a terminal, such as electric power distribution, water distribution, heating, ventilation and air conditioning (xe2x80x9cHVACxe2x80x9d), or other systems. Similarly, for a database relating to structure of a company, a user who wishes to re-structure a portion of the company may wish to check out a portion of the database reflecting the portion that he or she wishes to restructure.
There are a number of deficiencies with check-out/check-in schemes. For example, since, in check-out/check-in schemes, copies are made of the live database, a relatively large amount of storage may be required, particularly if a number of users have checked out tables from the database. In addition, check-out/check-in schemes typically do not allow tables to be checked out from a checked-out copy, that is, they do not provide a hierarchical check-out scheme. Using the above example in which a database is used in connection with design of an airport, if a user checks out a portion of the live database relating to a terminal, check-out/check-in arrangements do not allow a user to thereafter check out a portion of the database relating to a particular facility of the terminal from the checked-out portion. Accordingly, if a user wishes to check out tables from a checked out copy, he or she will first need to check the copy back in again.
In addition, typically in a check-out/check in scheme, when a user modifies a record in the a table of a live database after the table has been checked-out therefrom and before it is checked back in, a conflict will exist as between the copy of the record in the checked-out copy and the copy of the record in the live database. In that case, the conflict will not be discovered until the checked-out copy is checked back in again. Typically, a user who is using the checked-out copy will not discover that a record has been modified in the live database, and, similarly, the user who is using the live database will not discover that a record has been modified in the checked-out copy, until the checked out copy is checked in. As part of the check-in operation, a conflict resolution operation is performed during which the user who is checking the copy back in will determine which of the copies of the record is to survive in the live database. A problem can arise in that, if the copy has been checked out for some time, a number of conflicts might exist which will need to be resolved, which conflicts will represent wasted effort since only one record, the record from the copy or the record from the live database, will survive after the conflict resolution operation.
Furthermore, in a check-out/check-in scheme it is typically difficult for two users to collaborate using the checked-out tables. This results from the fact that, in a check-out/check-in scheme, usually the checked out tables are in the form of files. In that case, when one user is using a file, the file will be locked and unavailable to other users, particularly if the other users wish to make updates thereto.
The invention provides a new and improved system and method of identifying and resolving conflicts among versions of a database table.
In brief summary, the invention in one aspect provides a conflict resolution system for use in connection with a database management system database table comprising a plurality of records, at least some of the records being configured to be associated with a plurality of version records each associated with one of a like plurality of versions, including a parent version and a child version. The conflict resolution system comprises a conflict identification module and a conflict resolution module. The conflict identification module is configured to generate at least one query to enable the database management system to identify in the database table record versions for records for which a conflict exists between their parent and child versions. The conflict resolution module is configured to, for each record for which a conflict exists, receive information from a user indicating selection of one of the parent or child version and to generate at least one query to enable the database management system to synchronize the non-selected of the parent or child version to the selected of the parent or child version.
In another aspect, the invention provides a computer program product for use in connection with a computer to provide a conflict resolution system for use in connection with a database management system database table comprising a plurality of records, at least some of the records being configured to be associated with a plurality of version records each associated with one of a like plurality of versions, including a parent version and a child version. The computer program product comprises a machine-readable medium having encoded thereon a conflict identification module and a conflict resolution module. The conflict identification module is configured to enable the computer to generate at least one query to enable the database management system to identify in the database table record versions for records for which a conflict exists between their parent and child versions. The conflict resolution module is configured to enable the computer to, for each record for which a conflict exists, receive information from a user indicating selection of one of the parent or child version and to generate at least one query to enable the database management system to synchronize the non-selected of the parent or child version to the selected of the parent or child version.
In yet another aspect, the invention provides a method for use in connection with a database management system database table comprising a plurality of records, at least some of the records being configured to be associated with a plurality of version records each associated with one of a like plurality of versions, including a parent version and a child version. The method includes a conflict identification step of generating at least one query to enable the database management system to identify in the database table record versions for records for which a conflict exists between their parent and child versions, and a conflict resolution step of, for each record for which a conflict exists, receiving information from a user indicating selection of one of the parent or child version and generating at least one query to enable the database management system to synchronize the non-selected of the parent or child version to the selected of the parent or child version.